package com.koicarp.university.graudate.common.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author xiewenquan
 * @Date 2020-04-26
 */
@Slf4j
public class ExcelUtil {
	private ExcelUtil(){};

	private static final String DICT_SHEET_NAME = "下拉选字典";

	/**
	 * 读取指定位置下的Excecl
	 * @param path excel路径
	 * @param sheetNum 读取的sheet页数量
	 * @param startIndex 起始读取行数
	 * @param cellNum 读取列数
	 * @return
	 */
	public static List<Object[]> readExcel(String path,int sheetNum,int startIndex,int cellNum) throws Exception{
		boolean notRead = !UniversityUtil.isNotNull(path);
		if (notRead){
			return null;
		}
		InputStream is = new FileInputStream(path);
		return readExcel(is,sheetNum,startIndex,cellNum);
	}

	/**
	 * 读取指定位置下的Excecl
	 * @param in excel输入流
	 * @param sheetNum 读取的sheet页数量
	 * @param startIndex 起始读取行数
	 * @param cellNum 读取列数
	 * @return
	 */
	public static List<Object[]> readExcel(InputStream in,int sheetNum,int startIndex,int cellNum) throws Exception{
		boolean notRead = sheetNum < 1 || cellNum < 1;
		if (notRead){
			return null;
		}
		List<Object[]> result = new ArrayList<>();
		Workbook wb = WorkbookFactory.create(in);
		for (int i = 0; i < sheetNum;i++){
			Sheet sheet=wb.getSheetAt(i);
			List<Object[]> sheetList = readSheet( sheet, startIndex, cellNum);
			result.addAll(sheetList);
		}
		return result;
	}

	/**
	 * 读取每个Sheet页的数据
	 * @param sheet
	 * @param startIndex
	 * @param cellNum
	 * @return
	 */
	private static List<Object[]> readSheet(Sheet sheet, int startIndex, int cellNum) {
		List<Object[]> sheetList = new ArrayList<>();
		int i = startIndex;
		while (true) {
			Row row = sheet.getRow(i);
			i ++;
			if (row != null) {
				Object[] arr = new Object[cellNum];
				int blankCounts = 0;
				for (int j = 0; j < arr.length; j++) {
					Cell cell = row.getCell(j);
					String value = getValue(cell);
					if (value == null || "".equals(value)) {
						blankCounts++;
					}
					if (blankCounts == arr.length) { //一整行都是空的就不再往下读
						return sheetList;
						//break;
					}
					arr[j] = value;

				}
				sheetList.add(arr);
			} else {
				break;
			}
		}
		return sheetList;
	}

	/**
	 * 将Cell中的值转换成字符串
	 * @param cell
	 * @return
	 */
	private static String getValue(Cell cell) {
		if(cell!=null){
			if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
				// 返回布尔类型的值
				return String.valueOf(cell.getBooleanCellValue());
			} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

				if(DateUtil.isCellDateFormatted(cell)){
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					return sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
				}
				cell.setCellType(Cell.CELL_TYPE_STRING);
				// 返回数值类型的值
				return String.valueOf(cell.getStringCellValue());
			} else {
				//返回字符串类型的值
				return String.valueOf(cell.getStringCellValue());
			}
		}
		return null;
	}

	/**
	 * 将数据写入到Excel中
	 * @param headers 表头
	 * @param dataList 数据集
	 * @param dataNum 每个Sheet页的数据量
	 * @param startIndex 数据起始行
	 * @return XSSFWorkbook可以获取输出流写入到文件中，也可以下载用
	 */
	public static Workbook writeExcel(String[] headers,List<Object[]> dataList,int dataNum,int startIndex){
		/*
		Workbook workbook = new XSSFWorkbook();
		int sheetNum = 0;//当前sheet页码
		Sheet sheet = null;
		if(dataList != null && dataList.size() > 0){
			for (int i = 0;i < dataList.size();i++){
				if (i % dataNum == 0 ){
					sheet = workbook.createSheet("sheet"+sheetNum);
					if (headers != null && headers.length > 0){
						Row firstRow = sheet.createRow(0);//第一行表头
						//设置表头信息
						Cell[] headerCells = new Cell[headers.length];
						for (int j = 0; j < headerCells.length;j++){
							headerCells[j] = firstRow.createCell(j);
							headerCells[j].setCellValue(headers[j]);
							headerCells[j].setCellStyle(createDefaultHeaderCellStyle(workbook));
						}
					}
					sheetNum++;
				}
				Object[] data = dataList.get(i);
				int rownum = (i%dataNum) +startIndex;
				Row dataRow = sheet.createRow(rownum);
				Cell[] dataCells = new Cell[data.length];
				for (int j = 0;j < data.length;j++){
					dataCells[j] = dataRow.createCell(j);
					dataCells[j].setCellValue(String.valueOf(data[j]));
				}
			}
		}else{
			sheet = workbook.createSheet("sheet"+sheetNum);
			if (headers != null && headers.length > 0){
				Row firstRow = sheet.createRow(0);//第一行表头
				//设置表头信息
				Cell[] headerCells = new Cell[headers.length];
				for (int j = 0; j < headerCells.length;j++){
					headerCells[j] = firstRow.createCell(j);
					headerCells[j].setCellValue(headers[j]);
					headerCells[j].setCellStyle(createDefaultHeaderCellStyle(workbook));
				}
			}

		}

		return workbook;
		 */
		return writeExcel(headers,dataList,dataNum,startIndex,null);
	}

	/**
	 * 往专门设计的模板中写入数据
	 * @param dataList 数据集合
	 * @param dataNum 每个Sheet页的数据量
	 * @param startIndex 写入起始行
	 * @param path 文件路径
	 * @param sheetNum 页码数
	 * @return
	 */
	public static boolean writeDataToExcelTemplate(List<Object[]> dataList,int dataNum,int startIndex,String path,int sheetNum){

		try {
			InputStream in = new FileInputStream(path);
			Workbook workbook =  WorkbookFactory.create(in);
			Sheet sheet = workbook.getSheetAt(sheetNum);
			for (int i = 0;i < dataList.size();i++){
				Object[] data = dataList.get(i);
				int rownum = (i%dataNum) +startIndex;
				Row dataRow = sheet.createRow(rownum);
				Cell[] dataCells = new Cell[data.length];
				for (int j = 0;j < data.length;j++){
					dataCells[j] = dataRow.createCell(j);
					dataCells[j].setCellValue(String.valueOf(data[j]));
				}

			}
			OutputStream out = null;
			try {
				out = new FileOutputStream(path);
				workbook.write(out);
				out.flush();
			} catch (Exception e){
				log.error("写数据到Excel出错了",e);
				return false;
			} finally {
				if (workbook != null){
					workbook.close();
				}
				if (out != null){
					out.close();
				}

			}
			return true;
		} catch (Exception e) {
			log.error("写数据到Excel出错了",e);
		}
		return false;
	}

	/**
	 * 向Excel中写入数据(数据中有几列是下拉选情况)
	 * @param headers 表头
	 * @param dataList 数据集
	 * @param dataNum 每个Sheet页的数据量
	 * @param startIndex 写入数据起始行
	 * @param dropDownMap 列下拉选信息(key:第几列  value:下拉选内容)
	 * @return
	 */
	public static Workbook writeExcel(String[] headers,List<Object[]> dataList,int dataNum,int startIndex,Map<Integer,String[]> dropDownMap){
		Workbook workbook = new XSSFWorkbook();
		int sheetCount = 1;
		if (dataList != null && dataList.size() > 0 && dataNum > 0){
			if (dataList.size() % dataNum == 0){
				sheetCount = dataList.size()/dataNum;
			} else {
				sheetCount = dataList.size()/dataNum+1;
			}
		}
		Sheet[] sheets = new Sheet[sheetCount];
		for (int i = 0; i < sheetCount;i++){
			sheets[i] = workbook.createSheet("sheet"+i);
		}
		Sheet dictSheet = null;
		if (dropDownMap != null && dropDownMap.size() > 0){
			dictSheet = workbook.createSheet(DICT_SHEET_NAME);
			genearteOtherSheet(dictSheet,dropDownMap);
		}
		for (int sheetNum = 0; sheetNum < sheetCount;sheetNum++){
			Sheet sheet  = sheets[sheetNum];
			createDropDownList(sheet,dictSheet,dropDownMap,startIndex);
			//设置表头信息
			if (headers != null && headers.length > 0){
				Row firstRow = sheet.createRow(0);//第一行表头
				Cell[] headerCells = new Cell[headers.length];
				for (int i = 0; i < headerCells.length;i++){
					headerCells[i] = firstRow.createCell(i);
					headerCells[i].setCellValue(headers[i]);
					headerCells[i].setCellStyle(createDefaultHeaderCellStyle(workbook));
				}
			}
			//写入数据
			if (dataList != null && dataList.size() > 0 ){
				for (int i = sheetNum*dataNum; i < (sheetNum+1)*dataNum && i < dataList.size();i++){
					Object[] data = dataList.get(i);
					int rownum = (i%dataNum) +startIndex;
					Row dataRow = sheet.createRow(rownum);
					Cell[] dataCells = new Cell[data.length];
					for (int j = 0;j < data.length;j++){
						dataCells[j] = dataRow.createCell(j);
						dataCells[j].setCellValue(String.valueOf(data[j]));
					}
				}
			}

		}
		return workbook;
	}

	/**
	 * Excel中Sheet页生成下拉选列
	 * @param sheet sheet页对象
	 * @param dropDownMap 下拉选信息(key:第几列  value:下拉选内容)
	 * @param firstRow 从第几行开始生成下拉选
	 */
	private static void createDropDownList(Sheet sheet, Sheet dictSheet, Map<Integer, String[]> dropDownMap, int firstRow){
		if (dropDownMap == null || dropDownMap.size() == 0 || dictSheet == null){
			return;
		}
		for(Map.Entry<Integer,String[]> entry : dropDownMap.entrySet()){
			int colNum = entry.getKey();
			String[] arr = entry.getValue();
			char c = 65;
			char charts = (char) (c + colNum);
			String strFormula = DICT_SHEET_NAME+"!$"+charts+"$1:"+"$"+charts+"$"+arr.length;
			sheet.addValidationData(setDataValidation(dictSheet,strFormula,firstRow,colNum,999999,colNum));
		}

	}

	/**
	 * 默认表头样式
	 * @param wb
	 * @return
	 */
	private static CellStyle createDefaultHeaderCellStyle(Workbook wb){
		CellStyle style = wb.createCellStyle();
		//水平对齐方式（居中）
		style.setAlignment(HorizontalAlignment.CENTER);
		//垂直对齐方式（居中）
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		//创建字体设置
		Font baseFont = wb.createFont();
		//字体
		baseFont.setFontName("宋体");
		//大小
		baseFont.setFontHeightInPoints((short)11);
		//加粗
		baseFont.setBold(true);
		style.setFont(baseFont);
		return style;
	}


	// 设置并引用其他Sheet作为绑定下拉列表数据
	private static DataValidation setDataValidation(Sheet dictSheet, String strFormula, int firstRow, int firstCol, int endRow, int endCol) {
		// 表示A列1-59行作为下拉列表来源数据
		// String formula = "typelist!$A$1:$A$59" ;
		// 原顺序为 起始行 起始列 终止行 终止列
		//System.out.println("下拉列表来源数据---"+strFormula);
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) dictSheet);
		DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula);
		DataValidation dataValidation = dvHelper.createValidation(formulaListConstraint, regions);
		//这两行设置单元格只能是列表中的内容，否则报错
		dataValidation.setSuppressDropDownArrow(true);
		dataValidation.setShowErrorBox(true);
		return dataValidation;
	}

	// 创建下拉列表值存储工作表并设置值
	private static void genearteOtherSheet(Sheet dictSheet, Map<Integer,String[]> dropDownMap) {
		//char c = 65;
		if (dropDownMap == null || dropDownMap.size() == 0){
			return;
		}
		for (Map.Entry<Integer,String[]> entry : dropDownMap.entrySet()){
			int key = entry.getKey();
			String[] values = entry.getValue();
			for (int i = 0; i < values.length; i++){
				Row row = dictSheet.getRow(i);
				if (row == null){
					row = dictSheet.createRow(i);
				}
				Cell cell = row.getCell(key);
				if (cell == null){
					cell = row.createCell(key);
				}
				cell.setCellValue(values[i]);
			}
		}
	}
	/**
	 * 解析excel
	 * @param inp excel InputStream.
	 * @return 对应数据列表
	 */
	public static List<List<Object>> readExcel(InputStream inp) {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(inp);
            //获取地0个sheet的数据
			Sheet sheet = wb.getSheetAt(0);
			List<List<Object>> excels = new ArrayList<>();
            //遍历每一行数据
			int cellsNumber = 0;
			for (int i = 0; i <= sheet.getLastRowNum(); i++) {
				if (i == 0) {
                    //获取每一行总共的列数
					cellsNumber = sheet.getRow(i).getPhysicalNumberOfCells();
				}
				List<Object> excelRows = new ArrayList<>();
				// 遍历每一行中的每一列中的
				for (int j = 0; j < cellsNumber; j++) {
                    //i和j组成二维坐标可以定位到对应到单元格内
					Cell cell = sheet.getRow(i).getCell(j);
					//新加的处理小数点类型的
					//cell.setCellType(CellType.STRING);
					if (i >= 1) {
						//如果单元格到内容为空就设置为"null"代表的是无数据
						if (cell == null) {
							excelRows.add("null");
						} else {
                            //不是空值的单元格数据
							excelRows.add(getValueNew(cell));
							//excelRows.add(cell.getStringCellValue().trim());
						}
					} else {
                        //该数据为表格的表头信息，单独存储与集合的首位
						excelRows.add(getValueNew(cell));
					}
				}
				excels.add(excelRows);
			}
			return excels;
		} catch (Exception e) {
			log.error("导入excel错误 : " + e.getMessage());
			return null;
		} finally {
			try {
				if (wb != null) {
					wb.close();
				}
				if (inp != null) {
					inp.close();
				}
			} catch (Exception e) {
				log.error("导入excel关流错误 : " + e.getMessage());
			}
		}
	}

	/**
	 * 读取excel单元格的值
	**/
	public static String getValueNew(Cell cell) {
		String birthdayVal = null;
		switch (cell.getCellTypeEnum()) {
			case STRING:
				birthdayVal = cell.getRichStringCellValue().getString();
				break;
			case NUMERIC:
				if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//                    此处为double类型的,转成对应的String类型数据
					long longVal = Math.round(cell.getNumericCellValue());
					double doubleVal =  cell.getNumericCellValue();
					if(Double.parseDouble(longVal + ".0") == doubleVal){
						birthdayVal = Long.toString(longVal);
					} else{
						birthdayVal = Double.toString(doubleVal);
					}

				//birthdayVal = Integer.toString(new Double(cell.getNumericCellValue()).intValue());
				} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
					birthdayVal = DateToStr(cell.getDateCellValue());
				} else {
					birthdayVal = DateToStr(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
				}
				break;
		}
		return birthdayVal;
	}

	/*
	* date转String
	* */
	public static String DateToStr(Date date) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return format.format(date);
	}
}
